package com.jiantong.admin.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * Created by mz on 13/9/17.
 */
public class ExportOrImportUtils {


    public static void dataExportDownload(HttpServletResponse response,
                                          List<String> titles,
                                          String sheetName,
                                          List<LinkedHashMap<String, Object>> data) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);

        //表头
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(2, 12 * 256);
        sheet.setColumnWidth(3, 17 * 256);
        sheet.setDefaultRowHeight((short) 10);

        for (int i = 0; i < titles.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(titles.get(i));
            cell.setCellStyle(style);
        }

        //数据
        int rowNum = 1;
        for (LinkedHashMap<String, Object> map : data) {
            row = sheet.createRow(rowNum);
            String[] keys = new String[map.size()];
            map.keySet().toArray(keys);

            for (int i = 0; i < keys.length; i++) {
                HSSFCell cellData = row.createCell(i);
                Object value = map.getOrDefault(keys[i], null);
                if (value instanceof Long) {
                    cellData.setCellValue((Long) value);
                } else if (value instanceof Integer) {
                    cellData.setCellValue((Integer) value);
                } else if (value instanceof Double) {
                    cellData.setCellValue((Double) value);
                } else if (value instanceof Float) {
                    cellData.setCellValue((Float) value);
                } else if (value instanceof Date) {
                    cellData.setCellValue((Date) value);
                } else if (value == null) {
                    cellData.setCellValue(" ");
                } else {
                    cellData.setCellValue(String.valueOf(value));
                }
                cellData.setCellStyle(style);

            }
            rowNum++;
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(sheetName + ".xls", "UTF-8"));//默认Excel名称
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

    public static void dataExportDownload2(HttpServletResponse response,
                                           List<String> titles0,
                                           List<String> titles1,
                                           String sheetName,
                                           List<LinkedHashMap<String, Object>> data) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);

        //表头
        HSSFRow row = sheet.createRow(0);
        HSSFRow row1 = sheet.createRow(1);
        HSSFCell cell;
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(2, 12 * 256);
        sheet.setColumnWidth(3, 17 * 256);
        sheet.setDefaultRowHeight((short) 10);

        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 5));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 7));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 8, 10));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 11, 11));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 12, 12));

        for (int i = 0; i < titles0.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(titles0.get(i));
            cell.setCellStyle(style);
        }

        for (int i = 0; i < titles1.size(); i++) {
            cell = row1.createCell(i);
            cell.setCellValue(titles1.get(i));
            cell.setCellStyle(style);
        }

        //数据
        int rowNum = 2;
        for (LinkedHashMap<String, Object> map : data) {
            row = sheet.createRow(rowNum);
            String[] keys = new String[map.size()];
            map.keySet().toArray(keys);

            for (int i = 0; i < keys.length; i++) {
                HSSFCell cellData = row.createCell(i);
                Object value = map.getOrDefault(keys[i], null);
                if (value instanceof Long) {
                    cellData.setCellValue((Long) value);
                } else if (value instanceof Integer) {
                    cellData.setCellValue((Integer) value);
                } else if (value instanceof Double) {
                    cellData.setCellValue((Double) value);
                } else if (value instanceof Float) {
                    cellData.setCellValue((Float) value);
                } else if (value instanceof Date) {
                    cellData.setCellValue((Date) value);
                } else if (value == null) {
                    cellData.setCellValue(" ");
                } else {
                    cellData.setCellValue(String.valueOf(value));
                }
                cellData.setCellStyle(style);

            }
            rowNum++;
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(sheetName + ".xls", "UTF-8"));//默认Excel名称
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

    public static void UserExportDownload(HttpServletResponse response,
                                          List<String> titles
    ) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);

        //表头
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(2, 12 * 256);
        sheet.setColumnWidth(3, 17 * 256);
        sheet.setDefaultRowHeight((short) 10);

        for (int i = 0; i < titles.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(titles.get(i));
            cell.setCellStyle(style);
        }
        for (int j = 1; j < 1000; j++) {
            row = sheet.createRow(j);
        }


        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("批量用户模板.xls", "UTF-8"));//默认Excel名称
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

    public static void dataExportDownloadForDocument(HttpServletResponse httpServletResponse, List<String> strings, List<String> strings1, List<String> strings2, String sheetName, List<LinkedHashMap<String, Object>> data) throws IOException {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);

        //表头
        HSSFRow row = sheet.createRow(0);
        HSSFRow row1 = sheet.createRow(1);
        HSSFRow row2 = sheet.createRow(2);
        HSSFCell cell;
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(2, 12 * 256);
        sheet.setColumnWidth(3, 17 * 256);
        sheet.setDefaultRowHeight((short) 10);

        sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(0, 2, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(0, 2, 28, 28));

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 5));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 15));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 25));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 26, 27));

        sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 7));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 9));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 11));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 13));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 15));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 16, 17));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 18, 19));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 20, 21));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 22, 23));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 24, 25));



        for (int i = 0; i < strings.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(strings.get(i));
            cell.setCellStyle(style);
        }

        for (int i = 0; i < strings1.size(); i++) {
            cell = row1.createCell(i);
            cell.setCellValue(strings1.get(i));
            cell.setCellStyle(style);
        }

        for (int i = 0; i < strings2.size(); i++) {
            cell = row2.createCell(i);
            cell.setCellValue(strings2.get(i));
            cell.setCellStyle(style);
        }

        //数据
        int rowNum = 3;
        for (LinkedHashMap<String, Object> map : data) {
            row = sheet.createRow(rowNum);
            String[] keys = new String[map.size()];
            map.keySet().toArray(keys);

            for (int i = 0; i < keys.length; i++) {
                HSSFCell cellData = row.createCell(i);
                Object value = map.getOrDefault(keys[i], null);
                if (value instanceof Long) {
                    cellData.setCellValue((Long) value);
                } else if (value instanceof Integer) {
                    cellData.setCellValue((Integer) value);
                } else if (value instanceof Double) {
                    cellData.setCellValue((Double) value);
                } else if (value instanceof Float) {
                    cellData.setCellValue((Float) value);
                } else if (value instanceof Date) {
                    cellData.setCellValue((Date) value);
                } else if (value == null) {
                    cellData.setCellValue(" ");
                } else {
                    cellData.setCellValue(String.valueOf(value));
                }
                cellData.setCellStyle(style);

            }
            rowNum++;
        }

//        httpServletResponse.setContentType("application/octet-stream");
//        httpServletResponse.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(sheetName + ".xls", "UTF-8"));//默认Excel名称
//        httpServletResponse.flushBuffer();
//        workbook.write(httpServletResponse.getOutputStream());

        httpServletResponse.reset();
        httpServletResponse.setContentType("application/vnd.ms-excel;charset=UTF-8");
        try {
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
            String fileName = URLEncoder.encode(df.format(new Date())+"_"+sheetName, "UTF-8");
            httpServletResponse.addHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(sheetName + ".xls", "UTF-8"));
            // 定义输出类型
            ServletOutputStream out = httpServletResponse.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
